# -*- coding: utf-8 -*-

import logging
import traceback
import pymysql
import pandas as pd

from config import database_config

from sqlalchemy import create_engine, Table, Column, MetaData, select, func
from sqlalchemy.orm import Session, sessionmaker

import config

logger = logging.getLogger(config.flask_app_log)


def database_connect():
    connect = pymysql.connect(
        host=database_config['host'],
        port=database_config['port'],
        user=database_config['user'],
        passwd=database_config['passwd'],
        db=database_config['db'],
    )

    return connect


# 默认数据库连接
default_connect = database_connect()


def get_data_by_proc(proc_name='sp_get_f_m_c_index_py', params=(), columns=[], connect=default_connect,) -> pd.DataFrame:
    """
    调用存储过程获取数据
    :param proc_name:
    :param params:
    :param connect
    :param columns:
    :return:
    """
    try:

        cursor = connect.cursor()
        cursor.callproc(proc_name, params)
        # connect.commit()
        data = cursor.fetchall()
        df = pd.DataFrame(data, columns=columns)
        astype_df(df)
        return df

    except Exception as e:
        logger.error("error:", traceback.format_exc(e))
        return None

    finally:
        cursor.close()
        connect.close()
        pass
    pass


def astype_df(df):
    ignore_columns = ['fundId', 'endDate', 'NN']
    for column in df.columns:
        if column not in ignore_columns:
            df[column] = df[column].astype(float)
            pass
        pass
    pass


def pd_sql(sql_str, connect=default_connect, columns=[]) -> pd.DataFrame:
    df = pd.read_sql(sql=sql_str, con=connect, columns=columns)
    connect.close()
    return df


def db_tbl():
    # 数据库连接

    url = 'mysql+pymysql://%s:%s@%s:%d/%s' % (
        database_config['user'],
        database_config['passwd'],
        database_config['host'],
        database_config['port'],
        database_config['db']
    )
    engine = create_engine(url=url)
    connect = engine.connect()

    # 表：name和metadata不能带参数名
    table = Table('cm_user', MetaData(bind=engine), autoload=True, autoload_with=engine)
    # 列
    columns = table.columns
    print(columns)

    select_column = [table.c.username, table.c.cellphone]
    select_sql_str = select(select_column).where(table.c.userid < 10)
    user_list = connect.execute(select_sql_str).fetchall()
    print(user_list)

    # 回话
    session = Session(bind=engine)
    user = session.query(table).filter_by(username='admin').first()
    session.close()

    print(user)

    pass


if __name__ == '__main__':
    # 使用pandas.read_sql
    # sql_str = 'select username, cellphone from cm_user'
    # user_df = pd_sql(sql_str=sql_str, columns=['账户名称', '手机好吗'])
    # print(user_df.head())

    # sqlalchemy
    db_tbl()

    pass
